<?php

namespace app\modules\lenders\models\search;

use Yii;
use yii\base\Model;
use yii\data\ActiveDataProvider;
use app\modules\lenders\models\Lendinvestment;
use app\modules\lenders\models\Lender;

/**
 * LendinvestmentSearch represents the model behind the search form about `app\modules\lenders\models\Lendinvestment`.
 */
class LendinvestmentSearch extends Lendinvestment {

    public $start_querenshuTime; //开始进账日期
    public $end_querenshuTime; //结束进账日期
    public $start_qixiDate; //开始起息日期
    public $end_qixiDate; //结束起息日期
    public $start_generatedRightDate; //开始匹配日期
    public $end_generatedRightDate; //结束匹配日期
    public $start_expireDate; //开始到期日期
    public $end_expireDate; //结束到期日期
    public $userName; //理财客户

    /**
     * @inheritdoc
     */

    public function rules() {
        return [
            [['lendInvestID', 'isDeleted', 'receiveBankID', 'seqNo', 'isRedeemed', 'customerManagerID', 'teamManagerID', 'operateManagerID', 'yunYingManagerID', 'yunYingManagerTeamBuildNameID', 'subCompanyManagerID', 'VPID', 'companyID', 'deptID', 'addressID', 'phoneID'], 'integer'],
            [['start_querenshuTime', 'userName', 'lenderID', 'end_querenshuTime', 'start_expireDate', 'end_expireDate', 'start_qixiDate', 'end_qixiDate', 'start_generatedRightDate', 'end_generatedRightDate', 'lendType', 'expireDate', 'recycleType', 'investStatus', 'paymentType', 'createTime', 'createUser', 'updateTime', 'updateUser', 'comments', 'payBankID', 'contractNo', 'lenderNo', 'generatedRightDate', 'redeemDate', 'address', 'redeemType', 'querenshuTime', 'firstDate', 'redeemOverDate', 'customerManagerName', 'teamManagerName', 'operateManagerName', 'yunYingManagerName', 'yunYingManagerTeamBuildName', 'subCompanyManagerName', 'VPName', 'qixiDate', 'innerComments', 'companyName', 'deptName', 'oralContractNo', 'renewContractNo', 'renewDate', 'jixiaoDate', 'postType', 'payBankAmtDtl'], 'safe'],
            [['investAmt', 'investRate', 'redeemAmt', 'investMonth'], 'number'],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels() {
        return array_merge(parent::attributeLabels(), array(
            'start_querenshuTime' => '开始进账日期',
            'end_querenshuTime' => '结束进账日期',
            'start_expireDate' => '开始到期日期',
            'end_expireDate' => '结束到期日期',
            'start_qixiDate' => '开始起息日期',
            'end_qixiDate' => '结束起息日期',
            'start_generatedRightDate' => '开始匹配日期',
            'end_generatedRightDate' => '结束匹配日期',
            'userName' => '理财客户',
        ));
    }

    /**
     * @inheritdoc
     */
    public function scenarios() {
        // bypass scenarios() implementation in the parent class
        return Model::scenarios();
    }

    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function search($params) {
        $query = Lendinvestment::find();
//                ->select('recycleType,paymentType,address,contractNo,generatedRightDate,expireDate,generatedRightDate,qixiDate');
        $query->joinWith(['lenders as lenders']);
//        $query->select("lenders.userName");
//        $query->joinWith(['banks as banks']);
//        $query->select("banks.province,banks.city,banks.bankName,banks.branchBankName,banks.bankUserName,banks.cardID");
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

        if (!($this->load($params) && $this->validate())) {
         $query->where('1=0');
            return $dataProvider;
        }

        $query->andFilterWhere([
            'recycleType' => $this->recycleType,
            'paymentType' => $this->paymentType,
            'rah_lendinvestment.address' => $this->address,
//            'lenderID' => $this->lenderID,
        ]);
        //查询公司
        if (!empty($this->companyName)) {
            $company = explode('/', $this->companyName);
            $query->andFilterWhere(['in', 'companyName', $company]);
        }

        
        $query->andFilterWhere(['like', 'contractNo', $this->contractNo])
                ->andFilterWhere(['like', 'lenders.userName', $this->userName])
                ->andFilterWhere(['>=', 'querenshuTime', $this->start_querenshuTime])
                ->andFilterWhere(['<=', 'querenshuTime', $this->end_querenshuTime])
                ->andFilterWhere(['>=', 'expireDate', $this->start_expireDate])
                ->andFilterWhere(['<=', 'expireDate', $this->end_expireDate])
                ->andFilterWhere(['>=', 'qixiDate', $this->start_qixiDate])
                ->andFilterWhere(['<=', 'qixiDate', $this->end_qixiDate])
                ->andFilterWhere(['>=', 'generatedRightDate', $this->start_generatedRightDate])
                ->andFilterWhere(['<=', 'generatedRightDate', $this->end_generatedRightDate]);

        $session = \Yii::$app->session;
        $session->remove('sql_yueyuejin');
        //判断是否有数据，没有就不存session
        //转换sql语句
        $commandQuery = clone $query;
        $str = $commandQuery->createCommand()->getRawSql();
        //判断理财客户
        if (empty($params['LendinvestmentSearch']['userName'])) {
            $where = strstr($str, 'WHERE');
            $_sqlselect = 'select b.province,b.city,b.bankName,'
                    . 'b.branchBankName,b.bankUserName,b.cardID, '
                    . 'ROUND(10000 * le.investAmt * le.investRate / 100 / 12 * 30 / 30,2)  as investAmt,'
                    . 'le.contractNo,le.investRate,le.investAmt,'
                    . 'le.querenshuTime,le.expireDate,le.qixiDate,le.generatedRightDate FROM '
                    . 'rah_lendinvestment le LEFT JOIN (SELECT * FROM rah_bank GROUP BY tablePK) AS b ON  le.lenderID=b.tablePK ';
            $str_address = str_replace("`rah_lendinvestment`.`address`", "`le`.`address`", $where);
            $strSql = $_sqlselect . $str_address;
            //存入session sql
            $session->set('sql_yueyuejin', $strSql);
        } else {
            //带理财客户搜索条件
            $where = strstr($str, 'WHERE');
            $_sqlselect = 'select b.province,b.city,b.bankName,'
                    . 'b.branchBankName,b.bankUserName,b.cardID, '
                    . 'ROUND(10000 * le.investAmt * le.investRate / 100 / 12 * 30 / 30,2)  as investAmt,'
                    . 'le.contractNo,le.investRate,le.investAmt,'
                    . 'le.querenshuTime,le.expireDate,le.qixiDate,le.generatedRightDate FROM '
                    . 'rah_lendinvestment le'
                    . ' LEFT JOIN rah_lender lenders  ON  le.lenderID = lenders.lenderID'
                    . ' LEFT JOIN (SELECT * FROM rah_bank GROUP BY tablePK) AS b ON  le.lenderID=b.tablePK ';
            $str_address = str_replace("`rah_lendinvestment`.`address`", "`le`.`address`", $where);
            $strSql = $_sqlselect . $str_address;
            //存入session sql
            $session->set('sql_yueyuejin', $strSql);
        }
        return $dataProvider;
    }

}
